﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using ZLDC.Common.Estate;

namespace ZLDC.DAL.Estate
{
    public  class CallBoardDAL
    {
                                                                                                                        public static void p_call_board_save(int msg_id, string msg_title, string content, int user_id, string pub_username, string pub_dept, int msg_type, string reader, out string outmsg, out int newid, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@msg_id", msg_id));
            parms.Add(new SqlParameter("@msg_title", msg_title));
            parms.Add(new SqlParameter("@content", content));
            parms.Add(new SqlParameter("@user_id", user_id));
            parms.Add(new SqlParameter("@pub_username", pub_username));
            parms.Add(new SqlParameter("@pub_dept", pub_dept));
            parms.Add(new SqlParameter("@msg_type", msg_type));
            parms.Add(new SqlParameter("@reader", reader));

            SqlParameter pOutmsg = new SqlParameter("@outmsg", SqlDbType.VarChar, 256);
            pOutmsg.Direction = ParameterDirection.Output;
            parms.Add(pOutmsg);

            SqlParameter pNewid = new SqlParameter("@newid", SqlDbType.Int, 4);
            pNewid.Direction = ParameterDirection.Output;
            parms.Add(pNewid);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);


            db.execute_procedure("p_call_board_save", parms);

            outmsg = (pOutmsg.Value == System.DBNull.Value) ? "" : Convert.ToString(pOutmsg.Value);
            newid = (pNewid.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pNewid.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

        }

        public static void DeleteMsg(int userid,_Database db) 
        {
           
            try
            {
                string sql = "delete t_call_board where user_id = " + userid + " and msg_type = 1 and msg_id not in (select top 1 msg_id from t_call_board where msg_type = 1 and user_id = " + userid + " order by pub_date desc)";
                db.ExecuteSql(sql);
            }
            catch { }
            finally 
            {
                db.close();
            }
        }


        public static void DeleteMsg(_Database db) 
        {
            try
            {
                string sql = "delete from t_call_board where msg_type = 0 and msg_id not in (select top 10 msg_id from t_call_board where msg_type = 0 order by pub_date desc)";
                db.ExecuteSql(sql);
            }
            catch
            { }
            finally 
            {
                db.close();
            }
        }
                                                                                public static DataTable p_call_board_list_table(int page, int pagesize, string condition, ref int total, _Database db)
        {
            List<SqlParameter> parms = new List<SqlParameter>();

            parms.Add(new SqlParameter("@page", page));
            parms.Add(new SqlParameter("@pagesize", pagesize));
            parms.Add(new SqlParameter("@condition", condition));

            SqlParameter pTotal = new SqlParameter("@total", SqlDbType.Int, 4);
            pTotal.Direction = ParameterDirection.InputOutput;
            pTotal.Value = total;
            parms.Add(pTotal);
            SqlParameter pRetval = new SqlParameter("@retval", SqlDbType.Int, 4);
            pRetval.Direction = ParameterDirection.ReturnValue;
            parms.Add(pRetval);

            DataTable dt = null;
            dt = db.GetDataTable("p_call_board_list", parms);

            total = (pTotal.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pTotal.Value);

            int retval = (pRetval.Value == System.DBNull.Value) ? 0 : Convert.ToInt32(pRetval.Value);

            return dt;
        }

                                                        public static DataSet SelectDetailsByID(int id,_Database db) 
        {
            DataSet ds = null;
            try
            {
                string sql = "select * from t_call_board where msg_id=" + id + "";
                ds = db.Query(sql);
            }
            catch { }
            finally 
            {
                db.close();
            }
            return ds;
        }
                public static int DeleteCallBoard(int id,_Database db)
        {
            int row = 0;
            try
            {
                string sql = "delete from t_call_board where msg_id=" + id + "";
                row=db.ExecuteSql(sql);
            }
            catch { }
            finally 
            {
                db.close();
            }
            return row;
        }


                public static string GetNewTopInfo(_Database db)
        {
                        string sSql;
            System.Text.StringBuilder sRtn = new System.Text.StringBuilder();
            DataSet ds = null;

            try
            {
                                sSql = "SELECT SUBSTRING(CONVERT(varchar(20),pub_date,120),6,11) as pub_date,content FROM t_call_board WHERE msg_type = 1 and convert(char(8),pub_date,112) = convert(char(8),getdate(),112)";
                ds = db.Query(sSql);
            }
            catch { }
            finally { db.close(); }

            for (int n = 0; n < ds.Tables[0].Rows.Count; n++)
            {
                if (n % 2 == 0)
                {
                  
                    sRtn.Append("<font color='Red'>");
                    sRtn.Append(ds.Tables[0].Rows[n]["pub_date"].ToString() + "&nbsp;");
                    sRtn.Append("</font>");
                    sRtn.Append("<font color='#009900'><strong>");
                    sRtn.Append(ds.Tables[0].Rows[n]["content"].ToString());
                    sRtn.Append("</strong></font>&nbsp;&nbsp;");
                }
                else 
                {
                    sRtn.Append("<font color='red'>");
                    sRtn.Append(ds.Tables[0].Rows[n]["pub_date"].ToString() + "&nbsp;");
                    sRtn.Append("</font>");
                    sRtn.Append("<font color='blue'><strong>");
                    sRtn.Append(ds.Tables[0].Rows[n]["content"].ToString());
                    sRtn.Append("</strong></font>&nbsp;&nbsp;");
                }
            }
         
            return sRtn.ToString();
         
        }
    }
}
